#!/bin/bash

HIVE_HOME=/usr/bin/hive
#上个月1日
Last_Month_DATE=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
SET hive.enforce.bucketing=true;
SET hive.enforce.sorting=true;
SET hive.optimize.bucketmapjoin = true;
SET hive.auto.convert.sortmerge.join=true;
SET hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
SET hive.exec.parallel=true;
SET hive.exec.parallel.thread.number=8;
--矢量化查询
SET hive.vectorized.execution.enabled=true;
--读取零拷贝
SET hive.exec.orc.zerocopy=true;  
--插入数据
INSERT INTO itcast_dwd.customer_relationship_dwd PARTITION (payment_time_year, payment_time_month, payment_time_day)
SELECT id                                                                    AS id,
       customer_id                                                           AS customer_id,
       NVL(origin_type, -1)                                                  AS origin_type,
       payment_time                                                          AS payment_time,
       SUBSTR(payment_time, 12, 2)                                           AS payment_time_hour,
       NVL(itcast_clazz_id, -1)                                              AS itcast_clazz_id,
       NVL(creator, -1)                                                      AS creator,
       IF(origin_type = 'NETSERVICE' OR origin_type = 'PRESIGNUP', '1', '0') AS origin_type_stat,
       SUBSTR(payment_time, 1, 4)                                            AS payment_time_year,
       SUBSTR(payment_time, 6, 2)                                            AS payment_time_month,
       SUBSTR(payment_time, 9, 2)                                            AS payment_time_day
FROM itcast_ods.customer_relationship
WHERE deleted = 0
  AND customer_id IS NOT NULL
  AND payment_state = 'PAID'
  AND SUBSTR(payment_time, 1, 10) >= '${Last_Month_DATE}';
"